#!/usr/bin/env python
# coding=utf-8
# __author__ = 'Yunchao Ling'

import pymysql.cursors


def insert_assembly_summary_genbank(filepath: str):
    connection = pymysql.connect(host='10.10.119.245',
                                 port=3304,
                                 user='assembly',
                                 password='assembly',
                                 db='genbank_assembly',
                                 charset='utf8',
                                 cursorclass=pymysql.cursors.DictCursor)

    try:
        cursor = connection.cursor()

        sql = "INSERT INTO `genbank_assembly` VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"

        infile = open(filepath, "r", encoding="utf-8")
        infile.readline()
        infile.readline()
        for line in infile:
            line = line.rstrip("\n")
            print(line)
            splitline = line.split("\t")
            cursor.execute(sql, splitline)
            connection.commit()
        infile.close()
        cursor.close()

    finally:
        connection.close()


def insert_prokaryote_type_strain_report(filepath: str):
    connection = pymysql.connect(host='10.10.119.245',
                                 port=3304,
                                 user='assembly',
                                 password='assembly',
                                 db='genbank_assembly',
                                 charset='utf8',
                                 cursorclass=pymysql.cursors.DictCursor)

    try:
        cursor = connection.cursor()

        sql = "INSERT INTO `prokaryote_type_strain_report` VALUES (%s, %s, %s, %s)"

        infile = open(filepath, "r", encoding="utf-8")
        infile.readline()
        for line in infile:
            line = line.rstrip("\n")
            print(line)
            splitline = line.split("\t")
            cursor.execute(sql, splitline)
            connection.commit()
        infile.close()
        cursor.close()

    finally:
        connection.close()


if __name__ == '__main__':
    # insert_assembly_summary_genbank("D:/assembly_summary_genbank.txt")
    insert_prokaryote_type_strain_report("D:/prokaryote_type_strain_report.txt")
